﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Activation;
using System.Data.Odbc;
using System.Data;

namespace XrayService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "XrayService" in code, svc and config file together.
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class XrayService : IXrayService
    {
        //การดึงข้อมูล ยิ่งดึงจากหลาย column หลาย table ยิ่งทำให้ใช้เวลามากขึ้น

        string _patientDataSQL_Profile =
            "SELECT PA_PatMas.PAPMI_No, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB " +
            "FROM PA_PatMas " +
            "WHERE 1 = 1 ";

        string _patientDataSQL_Address =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID,  " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2,  " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_Type, PA_Adm.PAADM_EmergencyNo,  " +
                "PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime,PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime,   " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus, " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc,  " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc,   " +
                "ARC_InsuranceType.INST_Code,ARC_InsuranceType.INST_Desc  ,  " +
                "PA_Person.PAPER_StName, CT_Marital.CTMAR_RowId, CT_Marital.CTMAR_Desc, CT_SocialStatus.SS_RowId, CT_SocialStatus.SS_Desc ," +
                "CT_Nation.CTNAT_RowID, CT_Nation.CTNAT_Desc ," +
                "CT_CityArea.CITAREA_Code, CT_CityArea.CITAREA_Desc,   " +
                "CT_City.CTCIT_Code, CT_City.CTCIT_Desc,   " +
                "CT_Province.PROV_Code, CT_Province.PROV_Desc ,  " +
                "CT_CareProv.CTPCP_Code, CT_CareProv.CTPCP_Desc , CT_CareProv.CTPCP_SMCNo  " +
            "FROM PA_PatMas, PA_Adm, PAC_WARD, PA_AdmInsurance, ARC_AuxilInsurType , ARC_InsuranceType  , " +
                "PA_Person, CT_CityArea, CT_Province, CT_City , CT_Marital , CT_SocialStatus , CT_Nation,  CT_CareProv " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND   " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) AND  " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND   " +
                "(PA_AdmInsurance.INS_InsType_DR =* SYSTEM.ARC_InsuranceType.INST_RowId) AND  " +
                "(PA_Adm.PAADM_ADMNo  !=null) AND   " +
                "(PA_PatMas.PAPMI_RowId =* PA_Person.PAPER_PAPMI_DR) AND   " +
                "(PA_Person.PAPER_CityArea_DR =* CT_CityArea.CITAREA_RowId) AND   " +
                "(PA_Person.PAPER_CT_Province_DR =* CT_Province.PROV_RowId) AND   " +
                "(PA_Person.PAPER_CityCode_DR =* CT_City.CTCIT_RowId)  AND  " +
                "(PA_Person.PAPER_Marital_DR =*  CT_Marital.CTMAR_RowId) AND " +
                "(PA_Person.PAPER_Nation_DR  =* CT_Nation.CTNAT_RowId) AND " +
                "(PA_Person.PAPER_SocialStatus_DR =* CT_SocialStatus.SS_RowId) AND " +
                "(PA_Adm.PAADM_AdmDocCodeDR =* CT_CareProv.CTPCP_RowId) AND " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID) ";


        string _patientDataSQL_Admission =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_Type, PA_Adm.PAADM_EmergencyNo, " +
                "PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime,PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime," +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus, " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc " +
            "FROM PA_PatMas, PA_Adm, PAC_WARD " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) ";

        string _patientDataSQL_Payment =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_Type, PA_Adm.PAADM_EmergencyNo, " +
                "PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime,PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime, " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus, " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc, " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc, " +
                "ARC_InsuranceType.INST_Code,ARC_InsuranceType.INST_Desc " +
            "FROM PA_PatMas, PA_Adm, PAC_WARD, PA_AdmInsurance, ARC_AuxilInsurType , ARC_InsuranceType  " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) AND " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND " +
                "(PA_AdmInsurance.INS_InsType_DR =* SYSTEM.ARC_InsuranceType.INST_RowId) AND " +
                "( PA_Adm.PAADM_ADMNo  !=null) AND  " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID)  ";

        string _patientDataSQL_Custom =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Person.PAPER_NokAddress1, " +
                "CT_CityArea.CITAREA_Code, CT_CityArea.CITAREA_Desc, " +
                "CT_City.CTCIT_Code, CT_City.CTCIT_Desc, " +
                "CT_Province.PROV_Code, CT_Province.PROV_Desc, " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_Type, PA_Adm.PAADM_EmergencyNo, " +
                "PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime,PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime, " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus, " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc " +
            "FROM PA_Person, CT_CityArea, CT_Province, CT_City, PA_PatMas, PA_Adm, " +
                "ARC_AuxilInsurType, PA_AdmInsurance, PAC_WARD " +
            "WHERE (PA_PatMas.PAPMI_RowId =* PA_Person.PAPER_PAPMI_DR) AND " +
                "(PA_Person.PAPER_CityArea_DR =* CT_CityArea.CITAREA_RowId) AND " +
                "(PA_Person.PAPER_CT_Province_DR =* CT_Province.PROV_RowId) AND " +
                "(PA_Person.PAPER_CityCode_DR =* CT_City.CTCIT_RowId) AND " +
                "(PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) AND " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID) ";

        string _patientDataSQL_Ward =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime, PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime,PA_Adm.PAADM_EmergencyNo, " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime,  PA_Adm.Paadm_VisitStatus, " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc, " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc, " +
                "ARC_InsuranceType.INST_Code,ARC_InsuranceType.INST_Desc " +
            "FROM PA_PatMas, PA_Adm, PAC_WARD,PA_AdmInsurance, ARC_AuxilInsurType , ARC_InsuranceType " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) AND " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND " +
                "(PA_AdmInsurance.INS_InsType_DR =* SYSTEM.ARC_InsuranceType.INST_RowId) AND " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID) AND " +
                "(PA_Adm.PAADM_DischgDate  is NULL) AND " +
                "(PA_Adm.PAADM_ADMNo is not null) ";

        string _patientDataSQL_OPD =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2, " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime,PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime, PA_Adm.PAADM_EmergencyNo, " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus, " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc, " +
                "ARC_InsuranceType.INST_Code,ARC_InsuranceType.INST_Desc " +
            "FROM PA_PatMas, PA_Adm,PA_AdmInsurance, ARC_AuxilInsurType , ARC_InsuranceType " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND " +
                "(PA_Adm.PAADM_Type='Out Patient') AND " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND " +
                "(PA_AdmInsurance.INS_InsType_DR =* SYSTEM.ARC_InsuranceType.INST_RowId) AND " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID) AND  " +
                "(PA_Adm.PAADM_DischgDate  is NULL) AND " +
                " (PA_Adm.PAADM_ADMNo is not null)  ";

        string _patientDataSQL_DischargeDate =
            "SELECT PA_PatMas.PAPMI_No, PA_Adm.PAADM_ADMNo, PA_PatMas.PAPMI_ID, " +
                "PA_PatMas.PAPMI_Name3, PA_PatMas.PAPMI_Name, PA_PatMas.PAPMI_Name2,  " +
                "PA_PatMas.PAPMI_Sex_DR, PA_PatMas.PAPMI_DOB, " +
                "PA_Adm.PAADM_RowID, PA_Adm.PAADM_Type,  PA_Adm.PAADM_EmergencyNo,  " +
                "PA_Adm.PAADM_AdmDate, PA_Adm.PAADM_AdmTime, PA_Adm.PAADM_ConvertDate, PA_Adm.PAADM_ConvertTime,   " +
                "PA_Adm.PAADM_DischgDate, PA_Adm.PAADM_DischgTime, PA_Adm.Paadm_VisitStatus,  " +
                "PAC_WARD.WARD_Code, PAC_WARD.WARD_Desc,  " +
                "ARC_AuxilInsurType.AUXIT_Code, ARC_AuxilInsurType.AUXIT_Desc,   " +
                "ARC_InsuranceType.INST_Code,ARC_InsuranceType.INST_Desc  ,  " +
                "PA_Person.PAPER_StName, CT_Marital.CTMAR_RowId, CT_Marital.CTMAR_Desc, CT_SocialStatus.SS_RowId, CT_SocialStatus.SS_Desc ," +
                "CT_Nation.CTNAT_RowID, CT_Nation.CTNAT_Desc ," +
                "CT_CityArea.CITAREA_Code, CT_CityArea.CITAREA_Desc,   " +
                "CT_City.CTCIT_Code, CT_City.CTCIT_Desc,   " +
                "CT_Province.PROV_Code, CT_Province.PROV_Desc ,   " +
                "CT_CareProv.CTPCP_Code, CT_CareProv.CTPCP_Desc , CT_CareProv.CTPCP_SMCNo  " +
            "FROM PA_PatMas, PA_Adm, PAC_WARD, PA_AdmInsurance, ARC_AuxilInsurType , ARC_InsuranceType  , " +
                "PA_Person, CT_CityArea, CT_Province, CT_City , CT_Marital , CT_SocialStatus , CT_Nation,  CT_CareProv " +
            "WHERE (PA_Adm.PAADM_PAPMI_DR =* PA_PatMas.PAPMI_RowId) AND   " +
                "(PA_Adm.PAADM_CurrentWard_DR =* PAC_WARD.WARD_RowID) AND  " +
                "(PA_Adm.PAADM_RowID =* PA_AdmInsurance.INS_ParRef) AND   " +
                "(PA_AdmInsurance.INS_InsType_DR =* SYSTEM.ARC_InsuranceType.INST_RowId) AND  " +
                "(PA_Adm.PAADM_ADMNo  !=null) AND   " +
                "(PA_PatMas.PAPMI_No != null) AND  " +
                "(PA_PatMas.PAPMI_RowId =* PA_Person.PAPER_PAPMI_DR) AND   " +
                "(PA_Person.PAPER_CityArea_DR =* CT_CityArea.CITAREA_RowId) AND   " +
                "(PA_Person.PAPER_CT_Province_DR =* CT_Province.PROV_RowId) AND   " +
                "(PA_Person.PAPER_CityCode_DR =* CT_City.CTCIT_RowId)  AND  " +
                "(PA_Person.PAPER_Marital_DR =*  CT_Marital.CTMAR_RowId) AND " +
                "(PA_Person.PAPER_Nation_DR  =* CT_Nation.CTNAT_RowId) AND " +
                "(PA_Person.PAPER_SocialStatus_DR =* CT_SocialStatus.SS_RowId) AND " +
                "(PA_Adm.PAADM_AdmDocCodeDR =* CT_CareProv.CTPCP_RowId) AND " +
                "(PA_AdmInsurance.INS_AuxInsType_DR =* ARC_AuxilInsurType.AUXIT_RowID) ";

    


        private IEnumerable<MedtrakPatientData> LoadMedtrakToLINQ(string ConditionSQL, PatientDataMode DataMode)
        {
            //แปลงข้อมูลที่อยู่ใน Cache ให้เป็น class
            //การแปลง varchar เป็น string มีปัญหาน้อยเพราะเป็น reference type ถ้าไม่มีข้อมูลก็จะให้ค่าเป็น null เลย
            //การแปลง datetime ต้องระวังเพราะถ้าข้อมูลเป็น null เวลาเรียกใช้ .ToString("dd/MM/yyyy") จะ error จึงจะเป็นต้องเช็ค null ก่อน
            //การแปลง byte? หรือ nullable value type ตัวอื่นๆก็เช่นเดียวกัน
            switch (DataMode)
            {
                case PatientDataMode.Profile:
                    return LoadProfile(_patientDataSQL_Profile + ConditionSQL);
                case PatientDataMode.Address:
                    return LoadAddress(_patientDataSQL_Address + ConditionSQL);
                case PatientDataMode.Admission:
                    return LoadAdmission(_patientDataSQL_Admission + ConditionSQL);
                case PatientDataMode.Payment:
                    return LoadPayment(_patientDataSQL_Payment + ConditionSQL);
                case PatientDataMode.Custom:
                    return LoadCustom(_patientDataSQL_Custom + ConditionSQL);
                case PatientDataMode.Ward:
                    return LoadWard(_patientDataSQL_Ward + ConditionSQL);
                case PatientDataMode.OPD:
                    return LoadOPD(_patientDataSQL_OPD + ConditionSQL);
                case PatientDataMode.DischargeDate:
                    return LoadDischargeDate(_patientDataSQL_DischargeDate + ConditionSQL);
                case PatientDataMode.AdmissionDate:
                    return LoadDischargeDate(_patientDataSQL_DischargeDate + ConditionSQL);

                default:
                    break;
            }
            return null;
        }

        #region LoadCondition

        private IEnumerable<MedtrakPatientData> LoadProfile(string MedtrakSQL)
        {

            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Profile,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadAddress(string MedtrakSQL)
        {

            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();


            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Address,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         Address = p["PAPER_StName"].ToString(),
                         TambonID = p["CITAREA_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CITAREA_Code"]),
                         TambonName = p["CITAREA_Desc"].ToString(),
                         AmphurID = p["CTCIT_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CTCIT_Code"]),
                         AmphurName = p["CTCIT_Desc"].ToString(),
                         ProvinceID = p["PROV_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PROV_Code"]),
                         ProvinceName = p["PROV_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                          p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MarriageCode = p["CTMAR_RowId"].ToString(),
                         MarriageName = p["CTMAR_Desc"].ToString(),
                         OCCUPACode = p["SS_RowId"].ToString(),
                         OCCUPAName = p["SS_Desc"].ToString(),
                         NationCode = p["CTNAT_RowID"].ToString(),
                         NationName = p["CTNAT_Desc"].ToString(),
                         PhysicianCode = p["CTPCP_Code"].ToString(),
                         PhysicianName = p["CTPCP_Desc"].ToString(),
                         PhysicianSMCNO = p["CTPCP_SMCNo"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadAdmission(string MedtrakSQL)
        {

            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();


            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Admission,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                           p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadPayment(string MedtrakSQL)
        {
            //  _medtrakConnection.Open();
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();
            //  _medtrakConnection.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Payment,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                            p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadDischargeDate(string MedtrakSQL)
        {
            //  _medtrakConnection.Open();
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();
            //  _medtrakConnection.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.DischargeDate,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         Address = p["PAPER_StName"].ToString(),
                         TambonID = p["CITAREA_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CITAREA_Code"]),
                         TambonName = p["CITAREA_Desc"].ToString(),
                         AmphurID = p["CTCIT_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CTCIT_Code"]),
                         AmphurName = p["CTCIT_Desc"].ToString(),
                         ProvinceID = p["PROV_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PROV_Code"]),
                         ProvinceName = p["PROV_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                            p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MarriageCode = p["CTMAR_RowId"].ToString(),
                         MarriageName = p["CTMAR_Desc"].ToString(),
                         OCCUPACode = p["SS_RowId"].ToString(),
                         OCCUPAName = p["SS_Desc"].ToString(),
                         NationCode = p["CTNAT_RowID"].ToString(),
                         NationName = p["CTNAT_Desc"].ToString(),
                         PhysicianCode = p["CTPCP_Code"].ToString(),
                         PhysicianName = p["CTPCP_Desc"].ToString(),
                         PhysicianSMCNO = p["CTPCP_SMCNo"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadAdmissionDate(string MedtrakSQL)
        {
            //  _medtrakConnection.Open();
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();
            //  _medtrakConnection.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.AdmissionDate,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         Address = p["PAPER_StName"].ToString(),
                         TambonID = p["CITAREA_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CITAREA_Code"]),
                         TambonName = p["CITAREA_Desc"].ToString(),
                         AmphurID = p["CTCIT_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CTCIT_Code"]),
                         AmphurName = p["CTCIT_Desc"].ToString(),
                         ProvinceID = p["PROV_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PROV_Code"]),
                         ProvinceName = p["PROV_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                           p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MarriageCode = p["CTMAR_RowId"].ToString(),
                         MarriageName = p["CTMAR_Desc"].ToString(),
                         OCCUPACode = p["SS_RowId"].ToString(),
                         OCCUPAName = p["SS_Desc"].ToString(),
                         NationCode = p["CTNAT_RowID"].ToString(),
                         NationName = p["CTNAT_Desc"].ToString(),
                         PhysicianCode = p["CTPCP_Code"].ToString(),
                         PhysicianName = p["CTPCP_Desc"].ToString(),
                         PhysicianSMCNO = p["CTPCP_SMCNo"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadCustom(string MedtrakSQL)
        {
            //  _medtrakConnection.Open();
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();
            //  _medtrakConnection.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Custom,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         Address = p["PAPER_NokAddress1"].ToString(),
                         TambonID = p["CITAREA_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CITAREA_Code"]),
                         TambonName = p["CITAREA_Desc"].ToString(),
                         AmphurID = p["CTCIT_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["CTCIT_Code"]),
                         AmphurName = p["CTCIT_Desc"].ToString(),
                         ProvinceID = p["PROV_Code"] == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PROV_Code"]),
                         ProvinceName = p["PROV_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         AdmitType = p["PAADM_Type"].ToString(),
                         AdmitDateTime =
                           p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }
        private IEnumerable<MedtrakPatientData> LoadWard(string MedtrakSQL)
        {
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Admission,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         AdmitDateTime =
                           p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         WardID = p["WARD_Code"].ToString(),
                         WardName = p["WARD_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }

        private IEnumerable<MedtrakPatientData> LoadOPD(string MedtrakSQL)
        {
            OdbcDataAdapter da = new OdbcDataAdapter(MedtrakSQL, Global.MedtrakConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "Patient");
            da.Dispose();

            var pl = from p in ds.Tables["Patient"].AsEnumerable()
                     select new MedtrakPatientData
                     {
                         DataMode = PatientDataMode.Admission,
                         HN = p["PAPMI_No"].ToString().Replace("-", ""),
                         AN = p["PAADM_ADMNo"].ToString().Replace("-", ""),
                         PID = p["PAPMI_ID"].ToString(),
                         Title = p["PAPMI_Name3"].ToString(),
                         FirstName = p["PAPMI_Name"].ToString(),
                         LastName = p["PAPMI_Name2"].ToString(),
                         Sex = p["PAPMI_Sex_DR"] == System.DBNull.Value ? (byte?)null : Convert.ToByte(p["PAPMI_Sex_DR"]),
                         BirthDate = p["PAPMI_DOB"] == System.DBNull.Value ? (DateTime?)null : Convert.ToDateTime(p["PAPMI_DOB"]),
                         AdmitDateTime =
                          p["PAADM_ConvertDate"] == System.DBNull.Value || p["PAADM_ConvertTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_ConvertDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_ConvertTime"].ToString()),
                         DischargeDateTime =
                            p["PAADM_DischgDate"] == System.DBNull.Value || p["PAADM_DischgTime"] == System.DBNull.Value
                            ? (DateTime?)null : Convert.ToDateTime(Convert.ToDateTime(p["PAADM_DischgDate"]).ToString("dd/MM/yyyy") + " " + p["PAADM_DischgTime"].ToString()),
                         ////WardID = p["WARD_Code"].ToString(),
                         ////WardName = p["WARD_Desc"].ToString(),
                         PaymentID = p["AUXIT_Code"].ToString(),
                         PaymentName = p["AUXIT_Desc"].ToString(),
                         INSTCode = p["INST_Code"].ToString(),
                         INSTName = p["INST_Desc"].ToString(),
                         MedtrakRowID = (p["PAADM_RowID"]) == System.DBNull.Value ? (int?)null : Convert.ToInt32(p["PAADM_RowID"]),
                         ANOLD = p["PAADM_EmergencyNo"].ToString().Replace("-", ""),
                         VisitStatus = p["Paadm_VisitStatus"].ToString(),
                     };
            return pl;
        }
        #endregion


        public IEnumerable<MedtrakPatientData> GetPatientDatasByMedtrakRowID(int MedtrakRowID, PatientDataMode DataMode)
        {
            string conditionSQL = "AND PA_Adm.PAADM_RowID = '" + MedtrakRowID + "'";

            return LoadMedtrakToLINQ(conditionSQL, DataMode);
        }
    }
}
